using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WatiN.Core;
using System.Collections;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Threading;
using System.Data;
using System.Collections.Specialized;

namespace RAIDSparesTest003
{
    class ReadExcel : ReportTestBase
    {
        public string File;
        ApplicationClass app;
        Workbook book;
        Worksheet sheet;
        Range range;
        List<string> HeaderNames = new List<string>();
        System.Data.DataTable Table = new System.Data.DataTable();
        NameValueCollection NameLocation = new NameValueCollection();
        int ExcelPID = 0;

        public System.Data.DataTable GetExelDataTable
        {
            get
            {
                return Table;
            }
        }

        public ReadExcel(string[] testData)
            : base(testData)
        {
            rowType = "ReadExcel";
            //Create Excel 
            InitExcelApp();

            book = null;
            sheet = null;
            range = null;
        }

        public ReadExcel(string[] testData, NameValueCollection NameLocList)
            : base(testData)
        {
            rowType = "ReadExcel";
            //Create Excel 
            InitExcelApp();

            book = null;
            sheet = null;
            range = null;
            NameLocation = NameLocList;
        }

        /// <summary>
        /// Create Excel application and capture current excel process id for excel app
        /// </summary>
        private void InitExcelApp()
        {
            Process[] P0 = Process.GetProcessesByName("Excel");

            app = new ApplicationClass();

            if (P0.Length > 0)
            {
                ExcelPID = GetProcessID(P0);
            }
            else
            {
                Process[] processes = Process.GetProcessesByName("Excel");
                foreach (Process proc in processes)
                {
                    ExcelPID = proc.Id;
                }
            }
        }

        public override bool Run()
        {
            log.StartTest("Read Excel Spreadsheet");
            bool isSuccessful = parseParameters();

            if (isSuccessful & hasExport)
            {
                ReportExporter1 export = new ReportExporter1(def, new string[2] { ExportFormat, ExportFile });
                isSuccessful = RunChild(export);
            }
            // TODO: get some results & compare them to expected values. 
            if (isSuccessful)
            {
                Close();
            }
            else
            {
                TestUtilities tu = new TestUtilities();
                tu.GoHome(def);
            }
            log.EndTest();

            return (errorCount + failureCount + warningCount == 0);
        }

        public void Read(string File, string ExcelSheet) //TestDefinition def)
        {

            bool isSuccessful = parseParameters();

            try
            {
                //Set the object to current to the File
                StartTimer();
                //GetProcessID();

                //DateTime startTime = DateTime.Now;
                app.Visible = false;
                app.ScreenUpdating = false;
                app.DisplayAlerts = false;

                book = app.Workbooks.Open(File, Missing.Value, Missing.Value, Missing.Value
                                                  , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                 , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                , Missing.Value, Missing.Value, Missing.Value);
                sheet = (Worksheet)book.Worksheets[int.Parse(ExcelSheet)];


                LogTimer("Open document");
                //Console.WriteLine("Open document Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);

                StartTimer();
                //startTime = DateTime.Now;

                // sheet.Cells["A1:B3"] - To select muliple cells
                //object[,] values = (object[,])range.Value2; - To display all selected excel values in array

                if (true)
                {
                    //Retrieve the used cells in the Excel files
                    range = sheet.UsedRange;

                    if (NameLocation.Count > 0)
                    {
                        //Find the row with the first valid value in Excel
                        ProcessData(range);
                    }
                    else
                    {
                        // Reads simple excel file that does not need location of the columns. 
                        ReadSimpleExcel(range);
                    }

                    LogTimer("Get Data Range");
                }
                //Console.WriteLine("Get Data Range Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);


                ////////Display on the screen what is read from excel file
                //////ReadRange(range);
                //////ReadCells(range);
            }
            catch (Exception e)
            {
                AddException(e);
                Console.WriteLine(e);
            }
            finally
            {
                CloseExcel();
                if (ProcessExist(ExcelPID))
                {
                    KillProcess(ExcelPID);
                }
            }
        }

        /// <summary>
        /// Release sheet/workbook so excel application can quit
        /// </summary>
        private void CloseExcel()
        {
            try
            {
                
                book.Close(false, Missing.Value, Missing.Value);
                app.Quit();

                releaseObject(app);
                releaseObject(sheet);
                releaseObject(book);
                releaseObject(range);
                //releaseObject(NameLocation);

                //Keep checking if the process exist in the memory
                int i = 0;
                while (ProcessExist(ExcelPID) && i < 10000)
                {
                    Console.WriteLine(i++);
                }
            }
            catch (Exception ex)
            {
                AddException(ex);
                
            }
        }

        /// <summary>
        /// Check if process is running 
        /// </summary>
        /// <param name="pid">Process ID</param>
        /// <returns></returns>
        private bool ProcessExist(int pid)
        {
            Process[] RunningProcesses = Process.GetProcesses();
            foreach (Process p in RunningProcesses)
            {
                if (p.Id == pid)
                {
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        /// Release excel objects
        /// </summary>
        /// <param name="obj">current excel object to be released</param>
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.Write("Unable to release the Excel Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 

        /// <summary>
        /// Find the row with the first valid value in Excel
        /// </summary>
        /// <param name="range">Selected values from excel</param>
        public void ProcessData(Range range)
        {
            int column = 1;
            try
            {
                string str = string.Empty;
                for (int row = 1; row <= range.Rows.Count; row++)
                {
                    if ((range.Cells[row, column] as Range).Value2 != null)
                    {
                        str = (string)(range.Cells[row, column] as Range).Value2;

                        if (NameLocation.GetKey(0).ToString() == str)
                        {
                            GetExcelValues(row);
                            break;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                AddException(e);
            }
        }

        /// <summary>
        /// Reads simple excel file that does not need location of the columns. 
        /// Assumes that the first row is a column headers.
        /// Reads the values from the excel and stores it in the data table 
        /// </summary>
        /// <param name="range">All the used cells</param>
        public void ReadSimpleExcel(Range range)
        {
            DataRow DataNewRow;
            string cellText = string.Empty;

            try
            {
                for (int i = 1; i <= range.Rows.Count; i++)
                {
                    DataNewRow = Table.NewRow();
                    for (int j = 1; j <= range.Columns.Count; j++)
                    {
                        if (((Range)range.Cells[i, j]).Value2 != null)
                        {
                            cellText = ((Range)range.Cells[i, j]).Value2.ToString();
                        }
                        else
                        {
                            cellText = "";
                        }

                        if (i == 1)
                        {
                            DataColumn dcolColumn = new DataColumn(cellText, typeof(string));
                            Table.Columns.Add(dcolColumn);
                        }
                        else
                        {
                            DataNewRow[((Range)range.Cells[1, j]).Value2.ToString()] = cellText;
                        }
                    }
                    Table.Rows.Add(DataNewRow);
                }
            }
            catch (Exception e)
            {
                AddException(e);

            }
            RemoveBlankRows();
        }


        /// <summary>
        /// Read values that are spcified by column location from excel
        /// </summary>
        /// <param name="rownum">Row number of the valid values</param>
        public void GetExcelValues(int rownum)
        {
            //Create columns 
            for (int j = 0; j < NameLocation.Count; j++)
            {
                DataColumn dcolColumn = new DataColumn(NameLocation.GetKey(j).ToString(), typeof(string));
                Table.Columns.Add(dcolColumn);
            }

            DataRow DataNewRow;
            string celltext = string.Empty;
            for (int row = rownum; row <= range.Rows.Count; row++)
            {
                DataNewRow = Table.NewRow();

                for (int i = 0; i < NameLocation.Count; i++)
                {
                    string start = NameLocation[i] + row.ToString();
                    Range ColumnRange = sheet.get_Range(start, Missing.Value);
                    try
                    {
                        // Three diffrent ways to retrieve the value of the cell. Each way has it's own format. 
                        //celltext = (string)ColumnRange.Text;
                        //celltext = ColumnRange.Formula.ToString();
                        if (ColumnRange.Value2 != null)
                        {
                            celltext = ColumnRange.Value2.ToString();
                            if (celltext != NameLocation.GetKey(i).ToString())
                            {
                                DataNewRow[NameLocation.GetKey(i).ToString()] = celltext;
                            }
                        }
                        else
                        {
                            DataNewRow[NameLocation.GetKey(i).ToString()] = "";
                        }
                    }
                    catch (Exception e)
                    {

                        AddException(e);
                    }
                }
                celltext = "";
                Table.Rows.Add(DataNewRow);
            }

            RemoveBlankRows();

        }

        /// <summary>
        /// Removes blank rows from the data table
        /// </summary>
        private void RemoveBlankRows()
        {
            int i = 0;
            string ColumnName = NameLocation.GetKey(i);

            while (i <= Table.Rows.Count - 1)
            {
                if (Table.Rows[i][ColumnName].ToString().Trim() == string.Empty)
                {
                    Table.Rows.RemoveAt(i);
                }
                else
                {
                    i += 1;
                }
            }
        }

        /// <summary>
        /// Checks if the value is header
        /// </summary>
        /// <param name="HeaderName"></param>
        /// <returns></returns>
        public bool IsHeader(string HeaderName)
        {
            string[] ColumnNames = NameLocation.AllKeys;

            foreach (string name in ColumnNames)
            {
                if (name == HeaderName)
                {
                    return true;
                }
            }
            return false;
        }



        private void ReadRange(Range range)
        {
            DateTime startTime = DateTime.Now;

            object[,] values = (object[,])range.Value2;

            // Value2 is a two dimenial array dime one = row, dime two = column.
            Console.WriteLine("Col Count: " + values.GetLength(1).ToString());
            Console.WriteLine("Row Count: " + values.GetLength(0).ToString());

            // View the values
            Console.Write("\t");
            for (int j = 1; j <= values.GetLength(1); j++)
            {
                Console.Write("{0}\t", j);
            }
            Console.WriteLine();
            for (int i = 1; i <= values.GetLength(0); i++)
            {
                Console.Write("{0}\t", i);
                for (int j = 1; j <= values.GetLength(1); j++)
                {
                    Console.Write("{0}\t", values[i, j]);
                }
                Console.WriteLine();
            }

            Console.WriteLine("Range Read Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
        }


        private void ReadCells(Range range)
        {
            DateTime startTime = DateTime.Now;

            // View the values
            Console.Write("\t");
            for (int j = 1; j <= range.Columns.Count; j++)
            {
                Console.Write("{0}\t", j);
            }
            Console.WriteLine();
            for (int i = 1; i <= range.Rows.Count; i++)
            {
                Console.Write("{0}\t", i);
                for (int j = 1; j <= range.Columns.Count; j++)
                {
                    Console.Write("{0}\t", ((Range)range.Cells[i, j]).Value2);
                }
                Console.WriteLine();
            }

            Console.WriteLine("Cells Read Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
        }

        /// <summary>
        /// Kill process specified by id
        /// </summary>
        /// <param name="pid">Process ID</param>
        private void KillProcess(int pid)
        {
            try
            {
                Process process = Process.GetProcessById(pid);
                process.Kill();
            }
            catch (Exception procEx)
            {
                Console.WriteLine("Could not kill excel process Ex:" + procEx.Message);
            }

            //Keep checking if the process exist in the memory 
            int i = 0;
            while (ProcessExist(ExcelPID) && i < 10000)
            {
                i++;
            }
        }

        /// <summary>
        /// Gets the latest excel process id
        /// </summary>
        /// <param name="P0">Previous list of all the runing processes</param>
        /// <returns></returns>
        private int GetProcessID(Process [] P0)      
        {
            Process[] P1;
            int I, J;

            P1 = Process.GetProcessesByName("Excel");

            I = 0;

            if (P1.Length > 1)
            {
                for (I = 0; I < P1.Length; I++)
                {
                    for (J = 0; J < P0.Length; J++)
                        if (P0[J].Id == P1[I].Id) break;

                    if (J == P0.Length) break;
                }
            }

            Process P = P1[I];
            return P.Id;    
        }

        private bool parseParameters()
        {
            //NIPR1 | NIPR0 | Excel; &MYDOCUMENTS\NIPR&ITS.XLS; 3

            bool isSuccessful = false;
            if (parameters != null)
            {
                try
                {
                    if (parameters.Length > 0)
                    {
                        TestHelperFunctions1 helpers = new TestHelperFunctions1();
                        File = parameters[0];
                        //ExcelSheet = parameters[1];


                        isSuccessful = true;
                    }

                }
                catch (Exception e)
                {
                    AddException(e);
                    isSuccessful = false;
                }
            }
            return isSuccessful;
        }

        /// <summary>
        /// Finds row in the table with the specified field name and value
        /// </summary>
        /// <param name="value">specific value in the column</param>
        /// <param name="fieldName">Column name</param>
        /// <returns></returns>
        public string[] FindRow(string value, string fieldName)
        {
            //string expresion = "select * from Table where " + fieldName + " like '" + value + "'";
            DataRow[] RowFounded = Table.Select("[" + fieldName + "]='" + value + "'");

            String[] row = new string[] { " " };
            foreach (DataRow drow in RowFounded)
            {
                row = new String[drow.ItemArray.Length];
                for (int x = 0; x < drow.ItemArray.Length; x++)
                {
                    row[x] = drow[x].ToString();
                }
            }

            return row;
        }


    }
}